package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.dao.IPersonalInfoDao;
import com.eling.elcms.basedoc.model.PersonalInfo;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.core.dao.hibernate.search.SearchCondition;
import com.eling.elcms.core.util.PropertyUtils;
import com.eling.elcms.member.dao.IInterestDao;
import com.eling.elcms.member.model.Interest;
import com.eling.elcms.member.model.Member;
import com.eling.elcms.member.model.Member.DataSource;
import com.eling.elcms.member.model.Member.Status;
import com.eling.elcms.travel.dao.ITravelContractDao;
import com.eling.elcms.travel.model.TravelContract;
import com.eling.elcms.travel.model.view.ContractStatView;
import com.eling.elcms.travel.model.view.TravelContractView;
import com.eling.elcms.travel.model.view.contractGridValid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.*;
@Repository
public class TravelContractDaoHibernate extends GenericDaoHibernate<TravelContract, Long> implements ITravelContractDao{
	
	@Autowired
	private IPersonalInfoDao personalInfoDao;
	
	@Autowired
	private IInterestDao interestDao;
	
	public TravelContractDaoHibernate() {
		super(TravelContract.class);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ContractStatView> conutValid(Date signUpDate, Date signUpDateEnd) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  pro.`name`,  pt. NAME AS productType, COUNT(STATUS),  SUM(tt.price), pro.pkProduct,pt.pkProductType  ");
		sql.append(" FROM  travel_travelcontract tt 	");
		sql.append(" INNER JOIN travel_producttype pt ON pt.pkProductType = tt.pkProductType ");
		sql.append(" INNER JOIN travel_productrule pr ON pr.pkProductRule = tt.pkProductRule ");
		sql.append(" INNER JOIN travel_product pro ON pro.pkProduct = pr.pkProduct ");
		sql.append(" WHERE `status` = 'Valid' ");
		sql.append(" AND tt.signUpDate>=:signUpDate AND tt.signUpDate<=:signUpDateEnd ");
		sql.append(" GROUP BY ");
		sql.append(" pt.pkProduct,pt.name ");

		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("signUpDate", signUpDate)
				.setParameter("signUpDateEnd", signUpDateEnd).list();

		List<ContractStatView> cosList = new ArrayList<>();
		for (Object[] objects : objList) {
			if (objects[0] != null && objects[1] != null && !"".equals(objects[1])) {
				ContractStatView contractStat = new ContractStatView();

				contractStat.setProductName(objects[0].toString());
				contractStat.setProductType(objects[1].toString());
				contractStat.setContractCount(Integer.parseInt(objects[2].toString()));
				contractStat.setAllMoney(Double.parseDouble(objects[3].toString()));
				contractStat.setPkProduct(new Long(objects[4].toString()));
				contractStat.setPkProductType(new Long(objects[5].toString()));
				cosList.add(contractStat);
			}
		}
		return cosList;
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<contractGridValid> contractValid(Long pkProduct, Long pkProductType,Date signUpDate, Date signUpDateEnd) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT");
		sql.append(" 	tt.contactNo, per.NAME AS perName,  per.sex, per.phone, pro. NAME AS proName, pt. NAME AS ptName, tt.number, tt.signUpDate, SUM(tt.price) ");
		sql.append(" FROM travel_travelcontract tt	 ");
		sql.append(" INNER JOIN mem_member mm ON mm.pkMember = tt.pkMember ");
		sql.append(" INNER JOIN bd_personalinfo per ON per.pkPersonalInfo = mm.pkPersonalInfo ");
		sql.append(" INNER JOIN travel_producttype pt ON pt.pkProductType = tt.pkProductType ");
		sql.append(" INNER JOIN travel_productrule pr ON pr.pkProductRule = tt.pkProductRule ");
		sql.append(" INNER JOIN travel_product pro ON pro.pkProduct = pr.pkProduct ");
		sql.append(" WHERE tt.status = 'Valid' and pro.pkProduct = "+pkProduct+" AND pt.pkProductType = "+pkProductType+" ");
		sql.append(" AND tt.signUpDate>=:signUpDate AND tt.signUpDate<=:signUpDateEnd ");
		sql.append(" GROUP BY tt.pkTravelContract ");
		/*
		 * sql.append("  "); sql.append("  ");
		 */
		String str = sql.toString();
		System.out.println(str);
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("signUpDate", signUpDate)
				.setParameter("signUpDateEnd", signUpDateEnd).list();

		List<contractGridValid> gridValidList = new ArrayList<>();
		for (Object[] objects : objList) {
			if (objects[0] != null && objects[1] != null && !"".equals(objects[1])) {
				contractGridValid gridValid = new contractGridValid();

				gridValid.setContractNo(objects[0] == null ? null : objects[0].toString());
				gridValid.setMemberName(objects[1] == null ? null : objects[1].toString());
				gridValid.setMemberSex(objects[2] == null ? null : objects[2].toString());
				gridValid.setPhone(objects[3] == null ? null : objects[3].toString());
				gridValid.setProductName(objects[4] == null ? null : objects[4].toString());
				gridValid.setProductTypeName(objects[5] == null ? null : objects[5].toString());
				gridValid.setNumber(objects[0] == null ? 0 : new Integer(objects[6].toString()));
				gridValid.setSingUpDate(objects[0] == null ? null : (Date) objects[7]);
				gridValid.setAllPay(objects[0] == null ? 0 : new Double(objects[8].toString()));
				gridValidList.add(gridValid);
			}
		}
		return gridValidList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer queryOccupied(Long pkTravelContract) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(												      ");
		sql.append("		SELECT												      ");
		sql.append("			tc.pkTravelContract,									      ");
		sql.append("			tc.memberCertificates,									      ");
		sql.append("			ci.pkCIImplement,									      ");
		sql.append("			sr.pkSignUpRegister,									      ");
		sql.append("			ci.statementStatus,									      ");
		sql.append("			mat.adjusttypes,									      ");
		sql.append("			sr.exitGroup,										      ");
		sql.append("			tc.`status`										      ");
		sql.append("		FROM												      ");
		sql.append("			travel_checkinimplement ci								      ");
		sql.append("		LEFT JOIN travel_groupreserve gr ON ci.pkGroupReserve = gr.pkGroupReserve			      ");
		sql.append("		LEFT JOIN travel_planitemgroup pig ON pig.pkPlanItemGroup = gr.pkPlanItemGroup			      ");
		sql.append("		LEFT JOIN travel_signupregister sr ON pig.pkPlanItemGroup = sr.pkPlanItemGroup			      ");
		sql.append("		LEFT JOIN travel_travelcontract tc ON sr.pkTravelContract = tc.pkTravelContract			      ");
		sql.append("		LEFT JOIN (											      ");
		sql.append("			SELECT											      ");
		sql.append("				a.pkPlanItemGroup,								      ");
		sql.append("				GROUP_CONCAT(b.adjusttypes) adjusttypes						      ");
		sql.append("			FROM											      ");
		sql.append("				travel_planitemgroup a								      ");
		sql.append("			LEFT JOIN travel_mem_adjusttypes b ON a.pkPlanItemGroup = b.pkMemGroup			      ");
		sql.append("			GROUP BY										      ");
		sql.append("				a.pkPlanItemGroup								      ");
		sql.append("		) mat ON mat.pkPlanItemGroup = pig.pkPlanItemGroup						      ");
		sql.append("		WHERE												      ");
		sql.append("			(											      ");
		sql.append("				ci.statementStatus = 'NoStatement'						      ");
		sql.append("				OR ci.statementStatus IS NULL							      ");
		sql.append("				OR sr.exitGroup IS TRUE								      ");
		sql.append("				OR mat.adjusttypes LIKE '%Abolished%'						      ");
		sql.append("			)											      ");
		sql.append("		AND tc.pkTravelContract IS NOT NULL								      ");
		sql.append("		UNION ALL											      ");
		sql.append("			SELECT											      ");
		sql.append("				tc.pkTravelContract,								      ");
		sql.append("				tc.memberCertificates,								      ");
		sql.append("				ci.pkCIImplement,								      ");
		sql.append("				sr.pkSignUpRegister,								      ");
		sql.append("				ci.statementStatus,								      ");
		sql.append("				mat.adjusttypes,								      ");
		sql.append("				sr.exitGroup,									      ");
		sql.append("				tc.`status`									      ");
		sql.append("			FROM											      ");
		sql.append("				travel_checkinimplement ci							      ");
		sql.append("			LEFT JOIN travel_nonmemgroupreserve mgr ON mgr.pkNonMemGroupReserve = ci.pkNonMemGroupReserve ");
		sql.append("			LEFT JOIN travel_nonmembergroup ng ON ng.pkNonMemberGroup = mgr.pkNonMemberGroup	      ");
		sql.append("			LEFT JOIN travel_nonmemberregister nmr ON ng.pkNonMemberGroup = nmr.pkNonMemberGroup	      ");
		sql.append("			LEFT JOIN travel_signupregister sr ON sr.pkSignUpRegister = nmr.pkSignUpRegister	      ");
		sql.append("			LEFT JOIN travel_travelcontract tc ON sr.pkTravelContract = tc.pkTravelContract		      ");
		sql.append("			LEFT JOIN (										      ");
		sql.append("				SELECT										      ");
		sql.append("					a.pkNonMemberGroup,							      ");
		sql.append("					GROUP_CONCAT(b.adjusttypes) adjusttypes					      ");
		sql.append("				FROM										      ");
		sql.append("					travel_nonmembergroup a							      ");
		sql.append("				LEFT JOIN travel_nonmem_adjusttypes b ON a.pkNonMemberGroup = b.pkNonMemGroup	      ");
		sql.append("				GROUP BY									      ");
		sql.append("					a.pkNonMemberGroup							      ");
		sql.append("			) mat ON mat.pkNonMemberGroup = ng.pkNonMemberGroup					      ");
		sql.append("			WHERE											      ");
		sql.append("				(										      ");
		sql.append("					ci.statementStatus = 'NoStatement'					      ");
		sql.append("					OR ci.statementStatus IS NULL						      ");
		sql.append("					OR sr.exitGroup IS TRUE							      ");
		sql.append("					OR mat.adjusttypes LIKE '%Abolished%'					      ");
		sql.append("				)										      ");
		sql.append("			AND tc.pkTravelContract IS NOT NULL							      ");
		sql.append("	) v													      ");
		sql.append("WHERE 1=1	      ");
		if(pkTravelContract != null){
			sql.append(" AND v.pkTravelContract = "+pkTravelContract);
		}

		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();
		
		return objList.size();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Object> pageQueryByPoint(Member cond, Long pkCommunityData,
			Long pkServicePoint, Long pkInterest, Integer firstResult,
			Integer maxResults, Boolean queryCount, String pkOrg,Boolean exitMember) {

		List<Status> listStatus = PropertyUtils.getProperty(cond, "statusIn");
		String status = "";
		if (listStatus != null) {
			for (int i = 0; i < listStatus.size(); i++) {
				if (i == listStatus.size() - 1) {
					status = status + "'" + listStatus.get(i).name() + "'";
				} else {
					status = status + "'" + listStatus.get(i).name() + "'"
							+ ",";
				}
			}
		} else {
			status = "'Normal','Late'";
		}

		StringBuffer sql = new StringBuffer();
		if (exitMember != null && exitMember) {
			if (queryCount != null && queryCount) {
				sql.append("SELECT count(t.c) FROM (");
			}
			sql.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql.append("     	count(1) c ");
			} else {
				sql.append("     	this_.pkMember  ");
			}
			sql.append("     FROM  ");
			sql.append("     	mem_member this_  ");
			sql.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkMember = this_.pkMember	");
			
			if (pkInterest != null) {
				sql.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql.append("     WHERE  1=1  ");
			sql.append("     AND ttc.status='Valid' ");//有效的
			if (pkInterest != null) {
				sql.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql.append("     AND this_. STATUS IN (" + status + ")  ");
			sql.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
				//sql.append("     AND   ttc.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			/*sql.append("   AND ttc.pkTravelContract NOT IN ( ");
			sql.append("   SELECT  ttc2.pkTravelContract  FROM  travel_travelcontract ttc2 WHERE  ttc2.pkTravelContract NOT IN(");
			sql.append("   SELECT  ttc3.pkTravelContract  FROM  travel_certificatestransfer tce JOIN travel_travelcontract ttc3 ON tce.pkTravelContract=ttc3.pkTravelContract)  ");
			sql.append("   AND ttc2. STATUS = 'Valid'  )");*/
			if(queryCount != null && !queryCount){
				sql.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql.append("  GROUP BY this_.pkMember) t");
			}
			return getSession().createNativeQuery(sql.toString()).list();
		}else {
			if (queryCount != null && queryCount) {
				sql.append("SELECT count(t.c) FROM (");
			}
			sql.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql.append("     	count(1) c ");
			} else {
				sql.append("     	this_.pkMember  ");
			}
			sql.append("     FROM  ");
			sql.append("     	mem_member this_  ");
			sql.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql.append("  INNER JOIN travel_certificatestransfer tct ON tct.assignor = this_.pkMember");//转让
			sql.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkTravelContract = tct.pkTravelContract	");
			if (pkInterest != null) {
				sql.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql.append("     WHERE  1=1  ");
			if (pkInterest != null) {
				sql.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql.append("     AND this_. STATUS IN (" + status + ")  ");
			sql.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			if(queryCount != null && !queryCount){
				sql.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql.append("  GROUP BY this_.pkMember) t");
			}
			List<Object> list = getSession().createNativeQuery(sql.toString()).list();
			StringBuffer sql2 = new StringBuffer();
			if (queryCount != null && queryCount) {
				sql2.append("SELECT count(t.c) FROM (");
			}
			sql2.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql2.append("     	count(1) c ");
			} else {
				sql2.append("     	this_.pkMember  ");
			}
			sql2.append("     FROM  ");
			sql2.append("     	mem_member this_  ");
			sql2.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql2.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkMember = this_.pkMember	");
			//sql2.append("  INNER JOIN travel_certificatestransfer tct ON tct.assignor = this_.pkMember");//转让
			sql2.append("  JOIN travel_returncredentials trc ON trc.pkTravelContract=ttc.pkTravelContract ");//退证
			if (pkInterest != null) {
				sql2.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql2.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql2.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql2.append("     WHERE  1=1  ");
			//sql.append("     AND ttc.status='Valid' ");//有效的
			if (pkInterest != null) {
				sql2.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql2.append("     AND this_. STATUS IN (" + status + ")  ");
			sql2.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql2.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql2.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql2.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql2.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql2.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (pkOrg != null) {
				sql2.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			sql2.append("     AND  trc.flowStatus = 'Approved' ");
			if(queryCount != null && !queryCount){
				sql2.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql2.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql2.append("  GROUP BY this_.pkMember) t");
			}
			List<Object> list2 = getSession().createNativeQuery(sql2.toString()).list();
			for (Object object : list2) {
				list.add(object);
			}
			return list;
		}
	}

	//search
	@SuppressWarnings("unchecked")
	@Override
	public List<Object> searchMemPagingWithCard(SearchCondition sc,Member cond, Long pkCommunityData,
			Long pkServicePoint, Long pkInterest, Integer firstResult,
			Integer maxResults, Boolean queryCount, String pkOrg,Boolean exitMember) {

		List<Status> listStatus = PropertyUtils.getProperty(cond, "statusIn");
		String status = "";
		if (listStatus != null) {
			for (int i = 0; i < listStatus.size(); i++) {
				if (i == listStatus.size() - 1) {
					status = status + "'" + listStatus.get(i).name() + "'";
				} else {
					status = status + "'" + listStatus.get(i).name() + "'"
							+ ",";
				}
			}
		} else {
			status = "'Normal','Late'";
		}

		StringBuffer sql = new StringBuffer();
		if (exitMember != null && exitMember) {
			if (queryCount != null && queryCount) {
				sql.append("SELECT count(t.c) FROM (");
			}
			sql.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql.append("     	count(1) c ");
			} else {
				sql.append("     	this_.pkMember  ");
			}
			sql.append("     FROM  ");
			sql.append("     	mem_member this_  ");
			sql.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkMember = this_.pkMember	");
			
			if (pkInterest != null) {
				sql.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql.append("     WHERE  1=1  ");
			sql.append("     AND ttc.status='Valid' ");//有效的
			if (pkInterest != null) {
				sql.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql.append("     AND this_. STATUS IN (" + status + ")  ");
			sql.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			/*sql.append("   AND ttc.pkTravelContract NOT IN ( ");
			sql.append("   SELECT  ttc2.pkTravelContract  FROM  travel_travelcontract ttc2 WHERE  ttc2.pkTravelContract NOT IN(");
			sql.append("   SELECT  ttc3.pkTravelContract  FROM  travel_certificatestransfer tce JOIN travel_travelcontract ttc3 ON tce.pkTravelContract=ttc3.pkTravelContract)  ");
			sql.append("   AND ttc2. STATUS = 'Valid'  )");*/
			if (sc.getS() != null){
				sql.append(" AND ( personalin3_.idNumber LIKE '%"+sc.getS()+"%'  ");
				sql.append(" OR personalin3_.mobilePhone LIKE '%"+sc.getS()+"%'   ");
				sql.append(" OR personalin3_.name LIKE '%"+sc.getS()+"%')   ");
			}
			if(queryCount != null && !queryCount){
				sql.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql.append("  GROUP BY this_.pkMember) t");
			}
			return getSession().createNativeQuery(sql.toString()).list();
		}else {
			if (queryCount != null && queryCount) {
				sql.append("SELECT count(t.c) FROM (");
			}
			sql.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql.append("     	count(1) c ");
			} else {
				sql.append("     	this_.pkMember  ");
			}
			sql.append("     FROM  ");
			sql.append("     	mem_member this_  ");
			sql.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql.append("  INNER JOIN travel_certificatestransfer tct ON tct.assignor = this_.pkMember");//转让
			sql.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkTravelContract = tct.pkTravelContract	");
			if (pkInterest != null) {
				sql.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql.append("     WHERE  1=1  ");
			//sql.append("     AND ttc.status='Valid' ");//有效的
			if (pkInterest != null) {
				sql.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql.append("     AND this_. STATUS IN (" + status + ")  ");
			sql.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (sc.getS() != null){
				sql.append(" AND ( personalin3_.idNumber LIKE '%"+sc.getS()+"%'  ");
				sql.append(" OR personalin3_.mobilePhone LIKE '%"+sc.getS()+"%'   ");
				sql.append(" OR personalin3_.name LIKE '%"+sc.getS()+"%')   ");
			}
			if (pkOrg != null) {
				sql.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			if(queryCount != null && !queryCount){
				sql.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql.append("  GROUP BY this_.pkMember) t");
			}
			List<Object> list = getSession().createNativeQuery(sql.toString()).list();
			StringBuffer sql2 = new StringBuffer();
			if (queryCount != null && queryCount) {
				sql2.append("SELECT count(t.c) FROM (");
			}
			sql2.append("     SELECT  ");
			if (queryCount != null && queryCount) {
				sql2.append("     	count(1) c ");
			} else {
				sql2.append("     	this_.pkMember  ");
			}
			sql2.append("     FROM  ");
			sql2.append("     	mem_member this_  ");
			sql2.append("     LEFT OUTER JOIN bd_personalinfo personalin3_ ON this_.pkPersonalInfo = personalin3_.pkPersonalInfo  ");
			sql2.append(" 	 INNER JOIN travel_travelcontract ttc ON ttc.pkMember = this_.pkMember	");
			//sql2.append("  INNER JOIN travel_certificatestransfer tct ON tct.assignor = this_.pkMember");//转让
			sql2.append("  JOIN travel_returncredentials trc ON trc.pkTravelContract=ttc.pkTravelContract ");//退证
			if (pkInterest != null) {
				sql2.append("     INNER JOIN mem_member_interest interest11_ ON this_.pkMember = interest11_.pkMember  ");
			}
			if (pkCommunityData != null) {
				sql2.append("     INNER JOIN bd_contactaddress contactadd4_ ON personalin3_.pkPersonalInfo = contactadd4_.pkPersonalInfo  ");
			}
			if (pkServicePoint != null) {
				sql2.append("     inner  JOIN om_mempoint mem on this_.pkMember = mem.pkMember  ");
			}
			sql2.append("     WHERE  1=1  ");
			//sql.append("     AND ttc.status='Valid' ");//有效的
			if (pkInterest != null) {
				sql2.append("     AND interest11_.pkInterest = " + pkInterest + "  ");
			}
			sql2.append("     AND this_. STATUS IN (" + status + ")  ");
			sql2.append("     AND this_.deleteFlag = 'UnDeleted'  ");
			if (pkCommunityData != null) {
				sql2.append("     AND contactadd4_.pkCommunityData = "
						+ pkCommunityData + "  ");
				sql2.append("     AND contactadd4_.addressStatus = 'DefaultAddress'  ");
				sql2.append("     AND contactadd4_.deleteFlag = 0  ");
			}
			if (pkServicePoint != null) {
				sql2.append("     AND   mem.pkServicePoint = " + pkServicePoint + "");
			}
			if (pkOrg != null) {
				sql2.append("     AND   this_.pkOrganization in (" + pkOrg + ")");
			}
			if (pkOrg != null) {
				sql2.append("     AND   this_.datasource = '"
						+ DataSource.Government.name() + "'");
			}
			if (sc.getS() != null){
				sql2.append(" AND ( personalin3_.idNumber LIKE '%"+sc.getS()+"%'  ");
				sql2.append(" OR personalin3_.mobilePhone LIKE '%"+sc.getS()+"%'   ");
				sql2.append(" OR personalin3_.name LIKE '%"+sc.getS()+"%')   ");
			}
			sql2.append("     AND  trc.flowStatus = 'Approved' ");
			if(queryCount != null && !queryCount){
				sql2.append(" group by this_.pkMember ");
			}
			if (queryCount != null && !queryCount) {
				sql2.append("     LIMIT " + firstResult + "," + maxResults + "  ");
			}
			
			if (queryCount != null && queryCount) {
				sql2.append("  GROUP BY this_.pkMember) t");
			}
			List<Object> list2 = getSession().createNativeQuery(sql2.toString()).list();
			for (Object object : list2) {
				list.add(object);
			}
			return list;
		}
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> queryMemPk() {
		List<Long> resultList = new ArrayList<Long>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   ");
		sql.append("	mm.pkMember,    ");
		sql.append("	bp.`name`    ");
		sql.append("FROM    ");
		sql.append("	mem_member mm    ");
		sql.append("LEFT JOIN bd_personalinfo bp ON mm.pkPersonalInfo = bp.pkPersonalInfo    ");
		sql.append("WHERE    ");
		sql.append("	mm.pkMember IN (    ");
		sql.append("		SELECT    ");
		sql.append("			tc.assignor    ");
		sql.append("		FROM       ");
		sql.append("			travel_certificatestransfer tc    ");
		sql.append("	)     ");
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		for(Object[] o : list){
			String str = o[0].toString();
			Long l = Long.parseLong(str);
			resultList.add(l);
		}
		return resultList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Member> queryMember(String strCond) {
		List<Member> resultList = new ArrayList<Member>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT                                                                                                                   ");
		sql.append(" 	this_.pkMember AS pkMember1_151_2_,                                                                                     ");
		sql.append(" 	this_.pkCard AS pkCard12_151_2_,                                                                                        ");
		sql.append(" 	this_.createDate AS createDa2_151_2_,                                                                                   ");
		sql.append(" 	this_.pkCreater AS pkCreat13_151_2_,                                                                                    ");
		sql.append(" 	this_.dataSource AS dataSour3_151_2_,                                                                                   ");
		sql.append(" 	this_.deleteFlag AS deleteFl4_151_2_,                                                                                   ");
		sql.append(" 	this_.description AS descript5_151_2_,                                                                                  ");
		sql.append(" 	this_.memberNumber AS memberNu6_151_2_,                                                                                 ");
		sql.append(" 	this_.pkOrganization AS pkOrgan14_151_2_,                                                                               ");
		sql.append(" 	this_.pkPersonalInfo AS pkPerso15_151_2_,                                                                               ");
		sql.append(" 	this_.sosPhone1 AS sosPhone7_151_2_,                                                                                    ");
		sql.append(" 	this_.sosPhone2 AS sosPhone8_151_2_,                                                                                    ");
		sql.append(" 	this_.source AS source9_151_2_,                                                                                         ");
		sql.append(" 	this_. STATUS AS status10_151_2_,                                                                                       ");
		sql.append(" 	this_.version AS version11_151_2_,                                                                                      ");
		sql.append(" 	interest2_.pkMember AS pkMember1_151_4_,                                                                                ");
		sql.append(" 	interest3_.pkInterest AS pkIntere2_153_4_,                                                                              ");
		sql.append(" 	interest3_.pkInterest AS pkIntere1_149_0_,                                                                              ");
		sql.append(" 	interest3_.description AS descript2_149_0_,                                                                             ");
		sql.append(" 	interest3_.interesttype AS interest3_149_0_,                                                                            ");
		sql.append(" 	interest3_. NAME AS name4_149_0_,                                                                                       ");
		sql.append(" 	interest3_.version AS version5_149_0_,                                                                                  ");
		sql.append(" 	personalin4_.pkPersonalInfo AS pkPerson1_30_1_,                                                                         ");
		sql.append(" 	personalin4_.address AS address2_30_1_,                                                                                 ");
		sql.append(" 	personalin4_.pkAreaAddress AS pkAreaA27_30_1_,                                                                          ");
		sql.append(" 	personalin4_.birthday AS birthday3_30_1_,                                                                               ");
		sql.append(" 	personalin4_.cultureLevel AS cultureL4_30_1_,                                                                           ");
		sql.append(" 	personalin4_.electronicMail AS electron5_30_1_,                                                                         ");
		sql.append(" 	personalin4_.faith AS faith6_30_1_,                                                                                     ");
		sql.append(" 	personalin4_.graduateSchool AS graduate7_30_1_,                                                                         ");
		sql.append(" 	personalin4_.pkAddress AS pkAddre28_30_1_,                                                                              ");
		sql.append(" 	personalin4_.houseRegisterDetail AS houseReg8_30_1_,                                                                    ");
		sql.append(" 	personalin4_.idNumber AS idNumber9_30_1_,                                                                               ");
		sql.append(" 	personalin4_.idType AS idType10_30_1_,                                                                                  ");
		sql.append(" 	personalin4_.lunarBirthday AS lunarBi11_30_1_,                                                                          ");
		sql.append(" 	personalin4_.maritalStatus AS marital12_30_1_,                                                                          ");
		sql.append(" 	personalin4_.mobilePhone AS mobileP13_30_1_,                                                                            ");
		sql.append(" 	personalin4_.monthIncome AS monthIn14_30_1_,                                                                            ");
		sql.append(" 	personalin4_. NAME AS name15_30_1_,                                                                                     ");
		sql.append(" 	personalin4_.nationlity AS nationl16_30_1_,                                                                             ");
		sql.append(" 	personalin4_.pkNativePlace AS pkNativ29_30_1_,                                                                          ");
		sql.append(" 	personalin4_.phone AS phone17_30_1_,                                                                                    ");
		sql.append(" 	personalin4_.politicalParty AS politic18_30_1_,                                                                         ");
		sql.append(" 	personalin4_.position AS positio19_30_1_,                                                                               ");
		sql.append(" 	personalin4_.qq AS qq20_30_1_,                                                                                          ");
		sql.append(" 	personalin4_.residence AS residen21_30_1_,                                                                              ");
		sql.append(" 	personalin4_.sex AS sex22_30_1_,                                                                                        ");
		sql.append(" 	personalin4_.version AS version23_30_1_,                                                                                ");
		sql.append(" 	personalin4_.weChat AS weChat24_30_1_,                                                                                  ");
		sql.append(" 	personalin4_. WORK AS work25_30_1_,                                                                                     ");
		sql.append(" 	personalin4_.zipCode AS zipCode26_30_1_                                                                                 ");
		sql.append(" FROM                                                                                                                     ");
		sql.append(" 	mem_member this_                                                                                                        ");
		sql.append(" LEFT OUTER JOIN mem_member_interest interest2_ ON this_.pkMember = interest2_.pkMember                                   ");
		sql.append(" LEFT OUTER JOIN mem_interest interest3_ ON interest2_.pkInterest = interest3_.pkInterest                                 ");
		sql.append(" LEFT OUTER JOIN bd_personalinfo personalin4_ ON this_.pkPersonalInfo = personalin4_.pkPersonalInfo                       ");
		sql.append(" WHERE                                                                                                                    ");
		sql.append(" 	this_.pkMember IN ("+strCond+")                                                                                 ");
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		Member m = null;
		PersonalInfo p = null;
		Interest interest = null;
		for(Object[] o : list){
			m = new Member();
			p = new PersonalInfo();
			interest = new Interest();
			m.setPkMember(Long.parseLong(o[0].toString()));
			if(o[7]!=null){
				m.setMemberNumber(o[7].toString());
			}
			if(o[13]!=null){
				if(o[13].toString().equals("Normal")){
					m.setStatus(Status.Normal);
				}else if(o[13].toString().equals("Late")){
					m.setStatus(Status.Late);
				}
			}
			if(o[14]!=null){
				m.setVersion(Integer.parseInt(o[14].toString()));
			}
			p = personalInfoDao.get(Long.parseLong(o[22].toString()));
			m.setPersonalInfo(p);
			if(o[15]!=null){
				interest = interestDao.get(Long.parseLong(o[15].toString()));
				Set<Interest> set = new HashSet<Interest>();
				set.add(interest);
				m.setInterest(set);
			}
			resultList.add(m);
		}
		return resultList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public TravelContractView searchByMemberCertificates(String memberCertificates) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT");
		sql.append(" 	tt.memberCertificates, per.NAME AS perName,  per.idNumber, pro. NAME AS proName, pt. NAME AS ptName, tt.signUpDate,tt.pkTravelContract ");
		sql.append(" FROM travel_travelcontract tt	 ");
		sql.append(" INNER JOIN mem_member mm ON mm.pkMember = tt.pkMember ");
		sql.append(" INNER JOIN bd_personalinfo per ON per.pkPersonalInfo = mm.pkPersonalInfo ");
		sql.append(" INNER JOIN travel_producttype pt ON pt.pkProductType = tt.pkProductType ");
		sql.append(" INNER JOIN travel_productrule pr ON pr.pkProductRule = tt.pkProductRule ");
		sql.append(" INNER JOIN travel_product pro ON pro.pkProduct = pr.pkProduct ");
		
		sql.append(" WHERE tt.memberCertificates = '"+memberCertificates+"' ");
		
		
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		TravelContractView tc = new TravelContractView();
		for (Object[] objects : list) {
			tc.setMemberCer(objects[0]==null?null:objects[0].toString());
			tc.setPerName(objects[1]==null?null:objects[1].toString());
			tc.setPerId(objects[2]==null?null:objects[2].toString());
			tc.setProName(objects[3]==null?null:objects[3].toString());
			tc.setPtName(objects[4]==null?null:objects[4].toString());
			tc.setSignDate(objects[5]==null?null:(Date)objects[5]);
			tc.setPkTravelContract(new Long (objects[6].toString()));
		}
		
		return tc;
	}
}
